package svn_photo;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;

import com.oreilly.servlet.MultipartRequest;
import com.oreilly.servlet.multipart.DefaultFileRenamePolicy;

public class Photo {
		int fileSize=10*1024*1024;
//		private String uploadPath="C:\\workspace\\svn_jobs\\WebContent\\photoUpload";  //미선 파일경로
		
		
		private String uploadPath="C:\\Users\\jhta\\Desktop\\1403\\svn_jobs\\WebContent\\photoUpload";  //진규 파일경로
//		private String uploadPath="C:\\Users\\MISEON\\workspace\\svn_jobs\\WebContent\\photoUpload";//집 파일경로

		String fileEncoding="utf-8";
		
		String sql="";

		String driver = "oracle.jdbc.driver.OracleDriver";
		String url = "jdbc:oracle:thin:@192.168.10.122:1521:xe";
		String db_user = "JOBS";
		String db_pwd = "1234";
		
		
//		String url = "jdbc:oracle:thin:@127.0.0.1:1521:xe";
//		String db_user = "hr";
//		String db_pwd = "hr";

		Connection conn;
		photoVo vo;

	


		public Photo() {
			try {

				// 1)드라이버 로딩
				Class.forName(driver).newInstance();

				// 2)Connection 생성
				conn = DriverManager.getConnection(url, db_user, db_pwd);
			} catch (Exception ex) {
				ex.getStackTrace();
			}
		}

//------------------------------------Start 목록----------------------------------------		
		//검색!!
		public Vector<photoVo> list(int code) {
			Vector<photoVo> v = new Vector<>();
			
			sql="select * from (select f.serial as fserial,f.fileid,f.filename as filename,"
					+ "d.serial as dserial, d.code as code, d.mid as mid, d.pwd as pwd, d.project as project,"
					+ " d.summary as summary, d.party as party, d.usedpro as usedpro,d.workspace as workspace,"
					+ " d.content as content, d.mdate as mdate, d.gdate as gdate, d.jdate as jdate, d.hit as hit "
					+ "from photofile f right outer join photodata d on f.fileid= d.serial)a inner join photocode c "
					+ "on a.code=c.serial where code=?";
			
			//[1.아이디,2.제목,3.상세내용,4.등록일, 5.시리얼 번호]로 검색
			try {
				PreparedStatement ps= conn.prepareStatement(sql);
				ps.setInt(1, code);		
	
				ResultSet rs= ps.executeQuery();
				
				while(rs.next()){
					
					photoVo gv=new photoVo();
					gv.setSerial(rs.getInt("dserial"));
					gv.setGisu(rs.getInt("gisu"));
					gv.setClassname(rs.getString("classname"));
					gv.setProject(rs.getString("project"));
					gv.setSummary(rs.getString("summary"));
					gv.setGdate(rs.getString("gdate"));
					gv.setJdate(rs.getString("jdate"));
					gv.setFilename(rs.getString("filename"));
					
					v.add(gv);
				}
				
				
				rs.close();
				ps.close();

			} catch (Exception ex) {
				ex.printStackTrace();
			}
			return v;
		}
		
		
		public Vector<photoVo> codeList() {
			Vector<photoVo> v = new Vector<>();
			
			sql="select rownum no, c.* from (select * from photocode order by gisu desc)c";
			try {
				PreparedStatement ps= conn.prepareStatement(sql);
				
				ResultSet rs= ps.executeQuery();
				
				while(rs.next()){
					
					photoVo gv=new photoVo();
					gv.setNo(rs.getInt("no"));
					gv.setGisu(rs.getInt("gisu"));
					gv.setClassname(rs.getString("classname"));
					gv.setSdate(rs.getString("sdate").substring(0,10));
					gv.setEdate(rs.getString("edate").substring(0,10));
					gv.setSerial(rs.getInt("serial"));
					v.add(gv);
				}
				
				
				rs.close();
				ps.close();

			} catch (Exception ex) {
				ex.printStackTrace();
			}
			return v;
		}
		
		
		
		
//------------------------------------------Start 삽입-----------------------------------------------
		
//		public boolean insert() {
//			boolean flag = false;
//			try {
//				MultipartRequest mr = new MultipartRequest(vo.getRequest(), uploadPath, fileSize, fileEncoding,	new DefaultFileRenamePolicy());
//				
//				String mid = mr.getParameter("mid");
//				String pwd = mr.getParameter("pwd");
//				String project = mr.getParameter("project");
//				String summary = mr.getParameter("summary");
//				String party = mr.getParameter("party");
//
//				
//				String usedpro = mr.getParameter("usedpro");
//				String workspace = mr.getParameter("workspace");
//				String content = mr.getParameter("content");
//
//				
//				int gisu =Integer.parseInt(mr.getParameter("gisu").toString());
//				String classname = mr.getParameter("classname");
//				
//				String gdate= mr.getParameter("gdate");
//				String jdate= mr.getParameter("jdate");
//				
//				String orifile = mr.getOriginalFileName("filename");
//				String sysfile = mr.getFilesystemName("newfilename");
//				
//
//				
//				// db저장
//				String sql = "insert into photodata"
//						+ "(serial,"
//						+ "code,"
//						+ "mid, pwd, project, summary, party,"
//						+ "usedpro, workspace, content,imgname,filename,"
//						+ "mdate,gdate,jdate,hit) values"
//						+ "(data_no.nextval,"
//						+ "(select distinct c.serial  from photodata d left outer join photocode c on d.code=c.serial where c.gisu=?),"
//						+ "?,?,?,?,?,"
//						+ "?,?,?,?,?,"
//						+ "sysdate,to_date(?,'yyyy-MM-dd'),to_date(?,'yyyy-MM-dd'), 0)";
//				
//				// 이 셀렉이 문장이있어야 파일이 들어간다.  그리고 쿼리문은 끝까지 확인해줘야 한다.
//
//			
//				
//				try {
//					PreparedStatement ps = conn.prepareStatement(sql);

//					
//
//		
//					if (sysfile !=null){
//						ps.setString(11, sysfile);
//					}
//
//					int cnt = ps.executeUpdate();
//					if (cnt > 0) {
//						flag = true;
//					}
//					ps.close();
//					conn.close();
//					
//				} catch (Exception e) {
//					e.printStackTrace();
//				}
//			} catch (IOException e) {
//				e.printStackTrace();
//			}
//
//			return flag;
//		}
//		
//		
		public boolean insert() {
			boolean flag = false;
			try {
				MultipartRequest mr = new MultipartRequest(vo.getRequest(), uploadPath, fileSize, fileEncoding,	new DefaultFileRenamePolicy());
				
				String mid = mr.getParameter("mid");
				String pwd = mr.getParameter("pwd");
				String project = mr.getParameter("project");
				String summary = mr.getParameter("summary");
				String party = mr.getParameter("party");

				String usedpro = mr.getParameter("usedpro");
				String workspace = mr.getParameter("workspace");
				String content = mr.getParameter("content");
				int gisu =Integer.parseInt(mr.getParameter("gisu").toString());
				
				String gdate= mr.getParameter("gdate");
				String jdate= mr.getParameter("jdate");
				String sysfile = mr.getFilesystemName("filename");
				int code = 0;

				if (sysfile == null) {
					sysfile = "NoFile";
				}
				
				PreparedStatement ps = null;
				
				try {
					sql = "select serial from photocode where gisu = ?";
					ps = conn.prepareStatement(sql);
					ps.setInt(1, gisu);
					
					ResultSet rs = ps.executeQuery();
					if(rs.next()){
						code = rs.getInt("serial");
					}
					
					// db저장
					
					sql = "insert all "
						+ "		into photodata"
						+ "		(serial, code, mid, pwd, project, summary, party, usedpro, workspace, content, mdate, gdate, jdate, hit) values "
						+ "(data_no.nextval,  ?,  ?,   ?,    ?,        ?,      ?,      ?,        ?,        ?,   sysdate, substr(?, 1, 10) , substr(?, 1, 10) ,    0) ";
							
					if (!sysfile.equals("NoFile")) {
						sql += " into photofile(serial, fileid, filename)"
								+ " values(file_no.nextval, data_no.currval,?)";
					}
					sql += " select * from dual";// 이 셀렉이 문장이있어야 파일이 들어간다.

					ps = conn.prepareStatement(sql);
					ps.setInt(1, code);
					
					ps.setString(2, mid);
					ps.setString(3, pwd);
					ps.setString(4, project);
					ps.setString(5, summary);
					ps.setString(6, party);
					ps.setString(7,usedpro);
					ps.setString(8,workspace);
					ps.setString(9,content);
					
					ps.setString(10, gdate);
					ps.setString(11, jdate);
					
					
					if (!sysfile.equals("NoFile")) {
						ps.setString(12, sysfile);
					}
					
					int cnt = ps.executeUpdate();
					if (cnt > 0) {
						flag = true;
					}
					ps.close();
					conn.close();
				} catch (Exception e) {
					e.printStackTrace();
				}
			} catch (IOException e) {
				e.printStackTrace();
			}

			return flag;
		}

		
		public boolean codeInsert() {
				
				boolean flag= false;
				String sql="insert into photocode (serial, gisu, classname, sdate, edate)values"
						+ "(code_no.nextval,?,?,substr(?, 1, 10),substr(?, 1, 10))";//6월 30일 수정
				try{
					PreparedStatement ps = conn.prepareStatement(sql);
					ps.setInt(1, vo.getGisu());
					ps.setString(2, vo.getClassname());
					ps.setString(3, vo.getSdate());
					ps.setString(4, vo.getEdate());

					int cnt= ps.executeUpdate();
					
					if(cnt>0) flag=true;
					
					ps.close();
					conn.close();
					
				}catch(Exception ex){
					ex.printStackTrace();
				}
				
				return flag;
				
			}
		
		
		
//------------------------------------------End 삽입-----------------------------------------------	
		public Vector<photoVo> compare(){
			Vector<photoVo> c = new Vector<>();
			sql="select * from photocode order by gisu desc";
			
			try {
				PreparedStatement ps= conn.prepareStatement(sql);
				
	
				ResultSet rs= ps.executeQuery();
				
				while(rs.next()){
					
					photoVo gv=new photoVo();
					
					gv.setSerial(rs.getInt("serial"));
					gv.setGisu(rs.getInt("gisu"));
					gv.setClassname(rs.getString("classname"));	
					gv.setSdate(rs.getString("sdate").substring(0,10));
					gv.setEdate(rs.getString("edate").substring(0,10));
					
					
					c.add(gv);
				}
				
				rs.close();
				ps.close();

			} catch (Exception ex) {
				ex.printStackTrace();
			}
			return c;
		}
		
		public void endConn(){
			try {
				
				conn.close();
			} catch (Exception e) {
				// TODO: handle exception
			}
		}
		
	

		
//-----------------------------Start  수정-------------------------------------------//		
	
		
		public boolean modify(){
			boolean flag = false;
			try{
				MultipartRequest mr= new MultipartRequest(vo.getRequest(),uploadPath,fileSize,fileEncoding,new DefaultFileRenamePolicy());
				
				String project= mr.getParameter("project");
				String gdate= mr.getParameter("gdate");
				String jdate= mr.getParameter("jdate");
				String content= mr.getParameter("content");
				String pwd= mr.getParameter("pwd");
				
				
				String orifile = mr.getParameter("filename");
				String sysfile= mr.getFilesystemName("newfilename");
				
				String summary =mr.getParameter("summary");
				String usedpro =mr.getParameter("usedpro");
				String party =mr.getParameter("party");
				int serial = Integer.parseInt(mr.getParameter("serial"));
			
	
				
				PreparedStatement ps= null;
				int cnt=0;//처리결과
				
		
				//자동commit금지
				conn.setAutoCommit(false);
				
				//새로운 첨부파일이 존재하지 않으면(내용만수정)
				String sql="update photodata set project=?, mdate=sysdate, gdate=?,jdate=?,summary=?,"
				+ "party=?, usedpro=?,content=? "
				+ "where serial=?";		
				
				ps=conn.prepareStatement(sql);
				ps.setString(1, project);
				ps.setString(2, gdate);
				ps.setString(3, jdate);
				ps.setString(4, summary);

				ps.setString(5, party);
				ps.setString(6, usedpro);
				ps.setString(7, content);
				
				ps.setInt(8, serial);				
				
				cnt=ps.executeUpdate();
					
				if(cnt>0) flag=true;
				
				if(orifile != null){
					File file = new File(orifile);
					if(file.exists()){
						file.delete();
					}
					sql = "delete from photofile where fileid = ? and filename = ?";
					ps = conn.prepareStatement(sql);
					ps.setInt(1,serial);
					ps.setString(2, orifile);
					cnt=ps.executeUpdate();
					
					if(cnt>0) flag = true;
				}
				
				//새로운 첨부파일이 생성된 경우(내용+첨부파일이 모두 수정된 경우)
				if(sysfile != null && sysfile !=""){
					flag = false;
					sql = "insert into photofile values(file_no.nextval, ?, ?)";
					ps=conn.prepareStatement(sql);
					ps.setInt(1,serial);
					ps.setString(2, sysfile);
					
					cnt=ps.executeUpdate();
					if(cnt>0) flag = true;
				}
				
				if(flag) conn.commit();
				else conn.rollback();
				
				ps.close();
				conn.close();
				
			}catch(Exception ex){
				ex.printStackTrace();
			}
			return flag;
		}
		
		public void fileDel(String oldfile, int serial) {
			String sql = "delete from photofile where fileid=? and filename=?";
			try {
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setInt(1, serial);
				ps.setString(2, oldfile);
				ps.executeUpdate();

				File file = new File(uploadPath + "/" + oldfile);
				if (file.exists()) {
					file.delete();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	
		public boolean codeModify(){
			boolean flag=false;
		
			String sql="update photocode set gisu=?, classname=?, sdate=?, edate=? where serial=?";
			try{
				PreparedStatement ps= conn.prepareStatement(sql);

				ps.setInt(1, vo.getGisu());
				ps.setString(2, vo.getClassname());
				ps.setString(3, vo.getSdate());
				ps.setString(4, vo.getEdate());
				ps.setInt(5, vo.getSerial());

				
				int cnt=ps.executeUpdate();
				if(cnt>0) flag=true;
				
				ps.close();
				conn.close();
			}catch(Exception ex){
				ex.printStackTrace();
			}
			
			return flag;
			
		}
//-----------------------------End  수정-------------------------------------------//		
		
//-----------------------------Start  삭제-------------------------------------------//
		
		public boolean del(){
			boolean flag=false;
			String sql="delete from photodata where serial=?";
			try{
				PreparedStatement ps= conn.prepareStatement(sql);
				ps.setInt(1, vo.getSerial());
				
				int cnt=ps.executeUpdate();
				if(cnt>0) flag=true;
				
				sql="select * from (select f.serial as fserial,f.fileid,f.filename as filename,"
						+ "d.serial as dserial, d.code as code, d.mid as mid, d.pwd as pwd, d.project as project,"
						+ " d.summary as summary, d.party as party, d.usedpro as usedpro,d.workspace as workspace,"
						+ " d.content as content, d.mdate as mdate, d.gdate as gdate, d.jdate as jdate, d.hit as hit "
						+ "from photofile f inner join photodata d on f.fileid= d.serial)a inner join photocode c "
						+ "on a.code=c.serial where dserial=?";
				ps = conn.prepareStatement(sql);
				
				if(ps.executeUpdate()>0){
					flag = false;
					sql = "delete from photofile where fileid = ?";
					ps = conn.prepareStatement(sql);
					ps.setInt(1, vo.getSerial());
					
					cnt = ps.executeUpdate();
					if(cnt > 0) flag = true;
				}
				ps.close();
				conn.close();
			}catch(Exception ex){
				
				ex.printStackTrace();
			}
			
			
			return flag;
		}
			
		public boolean codeDel(){
			boolean flag=false;
			String sql="delete from photocode where serial=?";
			try{
				PreparedStatement ps= conn.prepareStatement(sql);
				ps.setInt(1, vo.getSerial());
				
				int cnt=ps.executeUpdate();
				if(cnt>0) flag=true;
				
				ps.close();
				conn.close();
			}catch(Exception ex){
				
				ex.printStackTrace();
			}
			
			
			return flag;
		}
		
		
//-----------------------------End  삭제-------------------------------------------//
		
		
		
		
		
		
//---------------------------Start 뷰-------------------------------------------------	
		//시리얼 번호가져와서 데이터 뿌리기
		public photoVo view(){
			photoVo v= new photoVo();
			sql="select * from (select f.serial as fserial,f.fileid,f.filename as filename,"
					+ "d.serial as dserial, d.code as code, d.mid as mid, d.pwd as pwd, d.project as project,"
					+ " d.summary as summary, d.party as party, d.usedpro as usedpro,d.workspace as workspace,"
					+ " d.content as content, d.mdate as mdate, d.gdate as gdate, d.jdate as jdate, d.hit as hit "
					+ "from photofile f right outer join photodata d on f.fileid= d.serial)a inner join photocode c "
					+ "on a.code=c.serial where dserial=?";
			
			
				try{
				PreparedStatement ps= conn.prepareStatement(sql);
				ps.setInt(1, vo.getSerial());

				
				ResultSet rs= ps.executeQuery();
				
				if(rs.next()){
			
					v.setGisu(rs.getInt("gisu"));
					v.setSerial(rs.getInt("dserial"));
					v.setMid(rs.getString("mid"));
					v.setPwd(rs.getString("pwd"));
					v.setProject(rs.getString("project"));
					v.setSummary(rs.getString("summary"));
					v.setParty(rs.getString("party"));
					v.setWorkspace(rs.getString("workspace"));
					v.setUsedpro(rs.getString("usedpro"));
					v.setFilename(rs.getString("filename"));
					v.setFserial(rs.getInt("fserial"));
					v.setContent(rs.getString("content"));
					v.setMdate(rs.getDate("mdate"));//현재날짜
					v.setGdate(rs.getString("gdate").substring(0,10));
					v.setJdate(rs.getString("jdate").substring(0,10));
					
				}
			
				rs.close();
				ps.close();
				conn.close();

			}catch(Exception ex){
				ex.printStackTrace();
			}
			return v;
		}
		
		

		
		public photoVo codeView(){
			photoVo v= new photoVo();
			System.out.println("Serial :" + vo.getSerial());
			sql="select * from photocode where serial=?";
			try{
				PreparedStatement ps= conn.prepareStatement(sql);
				ps.setInt(1, vo.getSerial());

				
				ResultSet rs= ps.executeQuery();
				
				if(rs.next()){
					v.setGisu(rs.getInt("gisu"));
					v.setSerial(rs.getInt("serial"));
					v.setSdate(rs.getString("sdate").substring(0,10));
					v.setEdate(rs.getString("edate").substring(0,10));
					v.setClassname(rs.getString("classname"));
					
					
				}
			
				rs.close();
				ps.close();
				conn.close();

			}catch(Exception ex){
				ex.printStackTrace();
			}
			return v;
		}
		
//----------------------------End 뷰---------------------------------------------		
//--------------------------- Start 기수검색------------------------------------
		
		
		public Vector<photoVo> cmbGisu() {
			Vector<photoVo> v = new Vector<>();
			
			sql="select * from photocode";

			try {
				PreparedStatement ps= conn.prepareStatement(sql);
				
				ResultSet rs= ps.executeQuery();
				
				while(rs.next()){
					
					photoVo gv=new photoVo();
					gv.setGisu(rs.getInt("gisu"));
					
					v.add(gv);
				}
				System.out.println(v.toString());
				
				rs.close();
				ps.close();

			} catch (Exception ex) {
				ex.printStackTrace();
			}
			return v;
		}
//---------------------------End 기수검색-------------------------------------------	
			
		public photoVo getVo() {
			return vo;
		}

		public void setVo(photoVo vo) {
			this.vo = vo;
		}
		public String getUploadPath() {
			return uploadPath;
		}


		public void setUploadPath(String uploadPath) {
			this.uploadPath = uploadPath;
		}

	}

